'''
基础查询 语法:
select 查询列表 from 表名
'''
import pymysql
conn = pymysql.connect(
    host='localhost',
    user='root',
    passwd='123456',
    db='db_library',
    port=3306,
    charset='utf8'
)
cur = conn.cursor()

# 1.    查询表中的单个字段
w = "SELECT name FROM test;"
cur.execute(w)
ret = cur.fetchall()
print(ret)
conn.commit()

# 2.    查询表中的多个字段
# w = "SELECT name,age,gender FROM test"
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)

# 3.    查询所有的字段
# w = "SELECT * FROM test"
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)

# 4.    起别名  AS就是起名字 但是在python中不显示,因为返回值是值,不是表格
#                也可以用空格
# w = "SELECT name AS '姓名',age AS '年龄' FROM test"
# w = "SELECT name '姓名',age '年龄' FROM test"
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)

# 5.    去重 DISTINCT   有很多的年龄，我只想知道都有哪些年龄段的人，重复的显示一次就好了
# w = "SELECT DISTINCT age FROM test"
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)

# 6.    拼接两个字段
# w = "SELECT CONCAT(name,',',gender) AS '名龄' FROM test"
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)

# 7.    判断是否为NULL     IFNULL 的括号里：第一个是要判断的字段 ，是空的就显示第二个位置的数或字符串，
#                           在拼接 CONCAT 里 要是有空值就会影响那一列的输出，那一列可能都会变成null ，所以可以用IFNULL判断一下
# w = "SELECT IFNULL(age,'0'),age FROM test"
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)









